# -*- coding: UTF-8 -*-
'''
@Project: yc-stock
@File: base.py
@Notes: 
@Author: zhuyoucheng
@Date: 2021/3/20 0020 15:07
'''
import pymysql
import json
import config

class PyMysql(object):

    def __init__(self, table):
        global MYSQL_DB
        self.table = table
        try:
            MYSQL_DB.ping()
        except Exception as e :
            print('------------------db init error----------------')
            print(e)
            MYSQL_DB = pymysql.connect(host=config.HOST, user=config.USERNAME, password=config.PASSWORD,
                                      database=config.DATABASE, port=config.PORT)
        self.db = MYSQL_DB
        self.cursor = MYSQL_DB.cursor()
        self.cursor=  self.db.cursor(pymysql.cursors.DictCursor)  # 游标对象


    #单条插入
    def insert(self, params={}):
        if params:
            p_keys = ','.join(params.keys())
            p_values = ', '.join(['%s'] * len(params))

            sql = "INSERT INTO `{table}` ({keys}) VALUES ({values})".format(table=self.table, keys=p_keys, values=p_values)
            try:
                if self.cursor.execute(sql, tuple(params.values())):
                    insert_id = self.db.insert_id()
                    self.db.commit()
                    return insert_id
            except Exception as e:
                print('-------------mysql-errors------------')
                print(e)
                self.db.rollback()

    #批量插入
    def create(self, params=[]):
        if params:
            if isinstance(params, dict):
                self.insert(params)
            else:
                for value in params:
                    p_keys = ','.join(value.keys())
                    p_values = ', '.join(['%s'] * len(value))
                sql = "INSERT INTO `{table}` ({keys}) VALUES ({values})".format(table=self.table, keys=p_keys, values=p_values)
                # 区别与单条插入数据，VALUES ('%s', '%s',  %s,  '%s', %s) 里面不用引号

                val = tuple(tuple(item.values()) for item in params)
                try:
                    # 执行sql语句
                    self.cursor.executemany(sql, val)
                    # 提交到数据库执行
                    self.db.commit()
                except:
                    # 如果发生错误则回滚
                    self.db.rollback()


    #查询数据
    def getByCondition(self, condition={}, field="*", page_size=0, page=1):
        try:
            if condition:
                sql = "SELECT `{field}` FROM `{table}` WHERE ".format(field=field, table=self.table)
                s_sql = ''
                for c_key in condition:
                    if not s_sql:
                        s_sql = "{key} = '{value}'".format(key=c_key, value=condition[c_key])
                        sql = sql+s_sql
                    else:
                        s_sql = " AND {key} = '{value}'".format(key=c_key, value=condition[c_key])
                        sql = sql+s_sql
                if page_size>0:
                    limit = page_size
                    offset = (page-1)*20
                    limit_sql = " LIMIT {limit} OFFSET {offset}".format(limit=limit, offset=offset)
                    sql = sql+limit_sql
                print('---------------sql--------------')
                print(sql)
                self.cursor.execute(sql)
                result = self.cursor.fetchall()
                return result
        except Exception as e:
            print('--------------Exception-------------')
            print(e)


    #更新数据
    def update(self, data={}, update={}):
        try:
            if data and update:
                sql = "UPDATE {table} SET".format(table=self.table)
                s_sql = ""
                for u_key in update:
                    if not s_sql:
                        s_sql = "{key} = '{value}'".format(key=u_key, value=update[u_key])
                    else:
                        s_sql = ", {key} = '{value}'".format(key=u_key, value=update[u_key])
                    sql = sql + s_sql
                d_sql = ""
                for d_key in data:
                    if not d_sql:
                        d_sql = " WHERE {key} = '{value}'".format(key=d_key, value=data[d_key])
                    else:
                        d_sql = ", WHERE {key} = '{value}'".format(key=d_key, value=data[d_key])
                    sql = sql + d_sql
                print(sql)
                result = self.cursor.execute(sql)
                print('result:'+result)
                self.db.commit()
                return True
        except Exception as e:
            print(e)
            self.db.rollback()
            return False

    #清空表
    def truncate_table(self):
        try:
            sql = 'truncate table '+self.table
            self.cursor.execute(sql)
        except Exception as e:
            print('---------error--------')
            print(e)
